package com.pioneer.dao;

import com.pioneer.beans.Job;

import com.pioneer.beans.Item;
import com.pioneer.beans.JobItem;

import java.sql.SQLException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;
import java.sql.ResultSet;

import java.util.Properties;
import java.util.List;
import java.util.ArrayList;
import java.util.StringTokenizer;


/**
 * Title:        Pioneer Application
 * Description:
 * Copyright:    Copyright (c) 2001
 * Company:      Pioneer Metals
 * @author Gary Chen
 * @version 1.0
 */

public class JdbcJobDAO
	extends JdbcDAO
	implements JobDAO {

	
    public JdbcJobDAO(Properties properties)
	throws DataAccessException
	{
		super(properties);
    }
	
	
	
	/**
	* This is the jobs maintenance section
	*/

	public List getJobs(String sortField)
	throws DataAccessException {

		Statement stat=null;
		ResultSet rs=null;
		List jobs=new ArrayList();
		try
		{
			stat=connection.createStatement();
			String query = "select * from job";			
			if (sortField!=null) {
				query = query + " order by "+sortField;				
			}
			else {
				query = query + " order by quote_num desc";
			}
			rs=stat.executeQuery(query);
			while (rs.next())
			{
				jobs.add(new Job(rs.getString("quote_num"),
					fromSqlDate(rs.getDate("job_date")),rs.getString("job_name"),
					rs.getString("address"),rs.getString("city"),
					rs.getString("state"),rs.getString("zip"),
					rs.getString("sales_rep"),rs.getString("reg_sales_man"),
					rs.getInt("qty"),rs.getString("type"),
					rs.getString("supplier_name"),rs.getString("supplier_contact"),
					rs.getString("contractor"),rs.getString("contractor_contact"),
					rs.getString("builder"),
					rs.getString("status"),	fromSqlDate(rs.getDate("ship_date")),
					rs.getString("terms"), rs.getString("notes"), fromSqlDate(rs.getDate("expiration_date"))
					)	);
			}
		}
		catch (SQLException ex)
		{
			throw new DataAccessException(ex.getMessage());
		}
		finally
		{
			try
			{
				if (rs!=null) rs.close();
				if (stat!=null) stat.close();
			}
			catch (SQLException ex)
			{
				System.err.println(ex.getMessage());
			}
		}		
		return jobs;
    }
	
	public String getLastQuoteNum()
	throws DataAccessException{
		Statement stat=null;
		ResultSet rs=null;
		Job job=null;
		try
		{
			stat=connection.createStatement();
			rs=stat.executeQuery("select max(sequence) as lastseq from job");
			int lastSeq = -1; 
			if (rs.next())	{
				lastSeq = rs.getInt("lastseq");
			}
			if (lastSeq>-1) {
				rs=stat.executeQuery("select quote_num as last from job where sequence="+lastSeq);
				if (rs.next()) {
					return rs.getString("last");
				}
			}
		}
		catch (SQLException ex)
		{
			throw new DataAccessException(ex.getMessage());
		}
		finally
		{
			try
			{
				if (rs!=null) rs.close();
				if (stat!=null) stat.close();
			}
			catch (SQLException ex)
			{
				System.err.println(ex.getMessage());
			}
		}
		return null;
    }

    public Job getJob(String quoteNum)
	throws DataAccessException {
		Statement stat=null;
		ResultSet rs=null;
		Job job=null;
		try
		{
			stat=connection.createStatement();
			rs=stat.executeQuery("select * from job where "+
			"quote_num='"+quoteNum+"'");
			if (rs.next())
			{
				job=new Job(rs.getString("quote_num"),
					fromSqlDate(rs.getDate("job_date")),rs.getString("job_name"),
					rs.getString("address"),rs.getString("city"),
					rs.getString("state"),rs.getString("zip"),
					rs.getString("sales_rep"),rs.getString("reg_sales_man"),
					rs.getInt("qty"),rs.getString("type"),
					rs.getString("supplier_name"),rs.getString("supplier_contact"),
					rs.getString("contractor"),rs.getString("contractor_contact"),
					rs.getString("builder"),
					rs.getString("status"),	fromSqlDate(rs.getDate("ship_date")),
					rs.getString("terms"), rs.getString("notes"),fromSqlDate(rs.getDate("expiration_date"))
					);				
			}
			if (job!=null) {
				List jobItems = getJobItems(job.getQuoteNum());
				job.setJobItems(jobItems);
			}
		}
		catch (SQLException ex)
		{
			throw new DataAccessException(ex.getMessage());
		}
		finally
		{
			try
			{
				if (rs!=null) rs.close();
				if (stat!=null) stat.close();
			}
			catch (SQLException ex)
			{
				System.err.println(ex.getMessage());
			}
		}
		return job;
    }
	
	private String toSqlDateString(java.util.Date date) {
		if (date!=null) {
			return "'"+(new java.sql.Date(date.getTime())).toString()+"'" ;
		}
		return null;
	}
	
	private java.util.Date fromSqlDate(java.sql.Date date) {
		if (date!=null) return new java.util.Date(date.getTime()) ;
		return null;
	}
	
    public void addJob(Job job)
	throws DataAccessException {
		//java.sql.Date jobDate=toSqlDate(job.getJobDate());
		//java.sql.Date shipDate=toSqlDate(job.getShipDate());
		String sqlString= "INSERT INTO job (quote_num,job_date,job_name,address," +
			"city,state,zip,sales_rep,reg_sales_man,qty,type,supplier_name,supplier_contact,"+
			"contractor,contractor_contact,builder,status,"+
			"ship_date,terms,notes,expiration_date) VALUES ('"+sqlSafe(job.getQuoteNum())+"',"+
		toSqlDateString(job.getJobDate())+",'"+sqlSafe(job.getJobName())+"','"+
		sqlSafe(job.getAddress())+"','"+sqlSafe(job.getCity())+"','"+
		sqlSafe(job.getState())+"','"+sqlSafe(job.getZip())+"','"+
		sqlSafe(job.getSalesRep())+"','"+sqlSafe(job.getRegSalesMan())+"','"+job.getQty()+"','"+
		sqlSafe(job.getType())+"','"+sqlSafe(job.getSupplierName())+"','"+
		sqlSafe(job.getSupplierContact())+"','"+
		sqlSafe(job.getContractorName())+"','"+
		sqlSafe(job.getContractorContact())+"','"+sqlSafe(job.getBuilderName())+"','"+
		sqlSafe(job.getStatus())+"',"+
		toSqlDateString(job.getShipDate())+",'"+sqlSafe(job.getTerms())+"','"+sqlSafe(job.getNotes())+"',"+
		toSqlDateString(job.getExpirationDate())+")";
		System.out.println(sqlString);
		updateStatement(sqlString);
    }

	public void updateJob(Job job)
	throws DataAccessException {
		System.out.println("Updating job date as "+job.getJobDateString());
		//java.sql.Date jobDate=toSqlDate(job.getJobDate());
		//java.sql.Date shipDate=toSqlDate(job.getShipDate());
		
		String sqlString="UPDATE job SET job_date = "+toSqlDateString(job.getJobDate())+
			", job_name='"+sqlSafe(job.getJobName())+
			"', address='"+sqlSafe(job.getAddress())+
			"', city='"+sqlSafe(job.getCity())+
			"', state='"+sqlSafe(job.getState())+
			"', zip='"+sqlSafe(job.getZip())+
			"', sales_rep='"+sqlSafe(job.getSalesRep())+
			"', reg_sales_man='"+sqlSafe(job.getRegSalesMan())+
			"', qty='"+job.getQty()+
			"', type='"+sqlSafe(job.getType())+
			"', supplier_name='"+sqlSafe(job.getSupplierName())+
			"', supplier_contact='"+sqlSafe(job.getSupplierContact())+
			"', contractor='"+sqlSafe(job.getContractorName())+
			"', contractor_contact='"+sqlSafe(job.getContractorContact())+
			"', builder='"+sqlSafe(job.getBuilderName())+
			"', status='"+sqlSafe(job.getStatus())+
			"', ship_date="+toSqlDateString(job.getShipDate())+
			", terms='"+sqlSafe(job.getTerms())+
			"', notes='"+sqlSafe(job.getNotes())+
			"', expiration_date="+toSqlDateString(job.getExpirationDate())+
			" WHERE quote_num='"+sqlSafe(job.getQuoteNum())+"'";
		System.out.println(sqlString);
		updateStatement(sqlString);
    }

    public void deleteJob(String quoteNum)
	throws DataAccessException {
		deleteJobItems(quoteNum);
		updateStatement("DELETE FROM job WHERE quote_num='"+sqlSafe(quoteNum)+"'");
    }

	
	/**
	* This is the items maintenance section
	*/

	public List getItems()
	throws DataAccessException {

		Statement stat=null;
		ResultSet rs=null;
		List items=new ArrayList();
		try
		{
			stat=connection.createStatement();
			rs=stat.executeQuery("select * from item");
			while (rs.next())
			{
				items.add(new Item(rs.getString("item_num"),
					rs.getString("description"),
					rs.getDouble("price"),rs.getDouble("cost")
					)	);
			}
		}
		catch (SQLException ex)
		{
			throw new DataAccessException(ex.getMessage());
		}
		finally
		{
			try
			{
				if (rs!=null) rs.close();
				if (stat!=null) stat.close();
			}
			catch (SQLException ex)
			{
				System.err.println(ex.getMessage());
			}
		}		
		return items;
    }
    public Item getItem(String itemName)
	throws DataAccessException {
		Statement stat=null;
		ResultSet rs=null;
		Item item=null;
		try
		{
			stat=connection.createStatement();
			rs=stat.executeQuery("select * from item where "+
			"item_num='"+itemName+"'");
			if (rs.next())
			{
				item=new Item(rs.getString("item_num"),
					rs.getString("description"),
					rs.getDouble("price"),rs.getDouble("cost")
					);				
			}
		}
		catch (SQLException ex)
		{
			throw new DataAccessException(ex.getMessage());
		}
		finally
		{
			try
			{
				if (rs!=null) rs.close();
				if (stat!=null) stat.close();
			}
			catch (SQLException ex)
			{
				System.err.println(ex.getMessage());
			}
		}
		return item;
    }
	
    public void addItem(Item item)
	throws DataAccessException {
		String sqlString= "INSERT INTO item (item_num,description,price,cost) VALUES ('"+
			sqlSafe(item.getItemNum())+"','"+
		sqlSafe(item.getDescription())+"','"+
		item.getPrice()+"','"+item.getCost()+"')";
		System.out.println(sqlString);
		updateStatement(sqlString);
    }

	public void updateItem(Item item)
	throws DataAccessException {
		String sqlString="UPDATE item SET description = '"+sqlSafe(item.getDescription())+
			"', price='"+item.getPrice()+
			"', cost='"+item.getCost()+
			"' WHERE item_num='"+sqlSafe(item.getItemNum())+"'";
		System.out.println(sqlString);
		updateStatement(sqlString);
    }

    public void deleteItem(String itemNum)
	throws DataAccessException {        
		updateStatement("DELETE FROM item WHERE item_num='"+sqlSafe(itemNum)+"'");
    }
	
	/**
	* This is the jobitems maintenance section
	*/

	public List getJobItems(String quoteNum)
	throws DataAccessException {

		Statement stat=null;
		ResultSet rs=null;
		List jobItems=new ArrayList();
		try
		{
			stat=connection.createStatement();
			rs=stat.executeQuery("SELECT * from job_items WHERE quote_num='"+quoteNum+"' order by sequence");
			while (rs.next())
			{
				jobItems.add(new JobItem(rs.getString("quote_num"),
					rs.getString("item_num"),
					rs.getString("item_desc"),
					rs.getInt("qty"), rs.getDouble("price")
					)	);
			}
		}
		catch (SQLException ex)
		{
			throw new DataAccessException(ex.getMessage());
		}
		finally
		{
			try
			{
				if (rs!=null) rs.close();
				if (stat!=null) stat.close();
			}
			catch (SQLException ex)
			{
				System.err.println(ex.getMessage());
			}
		}		
		return jobItems;
    }

    public void addJobItem(JobItem jobItem)
	throws DataAccessException {
		String sqlString= "INSERT INTO job_items "+
			"(quote_num,item_num,item_desc,qty,price) VALUES ('"+
			sqlSafe(jobItem.getQuoteNum())+"','"+
		sqlSafe(jobItem.getItemNum())+"','"+
		sqlSafe(jobItem.getItemDesc())+"','"+
		jobItem.getQty()+"','"+
		jobItem.getPrice()+"')";
		System.out.println(sqlString);
		updateStatement(sqlString);
    }

	public void updateJobItem(JobItem jobItem)
	throws DataAccessException {
		String sqlString="UPDATE job_items SET qty = "+jobItem.getQty()+
			", item_desc='"+jobItem.getItemDesc()+"' WHERE quote_num='"+sqlSafe(jobItem.getQuoteNum())+"' AND "+
			" item_num='"+sqlSafe(jobItem.getItemNum())+"'";
		System.out.println(sqlString);
		updateStatement(sqlString);
    }

    public void deleteJobItem(String quoteNum, String itemNum)
	throws DataAccessException {        
		updateStatement("DELETE FROM job_items WHERE quote_num='"+
			sqlSafe(quoteNum)+"' AND "+
			" item_num='"+sqlSafe(itemNum)+"'");
    }
	
	public void deleteJobItems(String quoteNum)
	throws DataAccessException {        
		updateStatement("DELETE FROM job_items WHERE quote_num='"+
			sqlSafe(quoteNum)+"'");
    }	
	
    public static void main(String[] args) {
		try
		{/*
		    Properties props=new Properties();
		    props.load(new java.io.FileInputStream("config"+java.io.File.separator+"db.prop"));
			JobDAO dao=new JdbcJobDAO(props);
			System.out.println("DAO loaded successfully");
			//Supplier supplier = new Supplier("supplierName","contact","address","city","CA","zip","213","fax");
			//Contractor contractor  = new Contractor("contractorName","Gary","phone","fax");
			Job job = new Job("1","1/1/1999","jobName","address","city",
				"CA","zip","salesRep","0","type","supplierName",
				"contractorName","done","1/1/1999","terms","notes");			
			Item item = new Item("123","new item",3.2,13);
			JobItem jobItem = new JobItem(job.getQuoteNum(),item.getItemNum(),4,4);
			//dao.addItem(item);
			//dao.addSupplier(supplier);			
			//dao.addContractor(contractor);			
			dao.addJob(job);
			dao.addJobItem(jobItem);
			
			//dao.getContractor("contractorName");
			//dao.getSupplier("supplierName");
			//dao.getJob("1");
			
			//dao.updateJob("1",job);
			//dao.updateContractor("contractorName",contractor);
			//dao.updateSupplier("supplierName",supplier);
			//dao.deleteJob("1");
			//dao.deleteSupplier("supplierName");
			//dao.deleteContractor("contractorName");
			*/
		}
		catch (Exception ex)
		{
			ex.printStackTrace();
		}
        //JdbcManifestDAO jdbcManifestDAO1 = new JdbcManifestDAO();
    }

}
